﻿Imports System.Data.SqlClient

Public Class frmStaffProfilesEndOfService

    'To detect if employee if already ended
    Public Ended As Boolean

    Sub FillEmployeeDetails()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From StaffProfiles where EmpNo=" & Me.txtEEmpNo.Text.Trim, cnn)
            Dim reader As SqlDataReader

            cnn.Open()
            reader = cmd.ExecuteReader
            While reader.Read
                Me.txtNameEn.Text = reader.Item("FullNameEn")
                Me.txtJobTitle.Text = reader.Item("Position")
                Me.txtHireDate.Text = CDate(reader.Item("HireDate")).ToString("dd-MMM-yyyy")
                Me.txtBranch.Text = reader.Item("Branch")

                Me.txtBS.Text = CDbl(reader.Item("SalaryBS")).ToString("N2")
                Me.txtHA.Text = CDbl(reader.Item("SalaryHA")).ToString("N2")
                Me.txtTA.Text = CDbl(reader.Item("SalaryTA")).ToString("N2")
                Me.txtFuel.Text = CDbl(reader.Item("SalaryFuel")).ToString("N2")
                Me.txtSupervision.Text = CDbl(reader.Item("SalarySupervision")).ToString("N2")
                Me.txtTuitionFess.Text = CDbl(reader.Item("SalaryTuitionFees")).ToString("N2")
                Me.txtMobileAllowance.Text = CDbl(reader.Item("SalaryMobile")).ToString("N2")
                Me.txtOthers.Text = CDbl(reader.Item("Others")).ToString("N2")
                Me.txtTotalSalary.Text = CDbl(reader.Item("TotalSalary")).ToString("N2")

                'Fill end of service details
                If Ended = True Then
                    Me.RResignation.Enabled = False
                    Me.RTermination.Enabled = False
                    Me.DTPEndOfService.Enabled = False
                    Me.txtComments.ReadOnly = True
                    Me.btnSave.Enabled = False

                    If reader.Item("EndOfServiceType") = "Resignation" Then
                        Me.RResignation.Checked = True
                    ElseIf reader.Item("EndOfServiceType") = "Termination" Then
                        Me.RTermination.Checked = True
                    End If
                    Me.txtSalary.Text = CDbl(reader.Item("EndOfServiceSalary")).ToString("N2")
                    Me.txtPeriodHiredYears.Text = reader.Item("HiredYears")
                    Me.txtPeriodHiredMonths.Text = reader.Item("HiredMonths")
                    Me.txtBenefits.Text = CDbl(reader.Item("EndOfServiceBenefits")).ToString("N2")
                    Me.txtComments.Text = reader.Item("Comments")
                End If
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnCalculate_Click(sender As System.Object, e As System.EventArgs) Handles btnCalculate.Click
        Try
            Dim PeriodMonths, Years, Months As Integer

            PeriodMonths = DateDiff(DateInterval.Month, CDate(Me.txtHireDate.Text), Me.DTPEndOfService.Value)
            Years = (PeriodMonths - (PeriodMonths Mod 12)) / 12
            Months = PeriodMonths Mod 12

            Me.txtSalary.Text = CDbl(CDbl(Me.txtBS.Text) + CDbl(Me.txtHA.Text)).ToString("N2")
            Me.txtPeriodHiredYears.Text = Years
            Me.txtPeriodHiredMonths.Text = Months

            If Years < 2 Then
                Me.txtBenefits.Text = "0.00"

            ElseIf Years >= 2 AndAlso Years < 5 Then
                Dim Benfit As Double = Years * 0.5 * CDbl(Me.txtSalary.Text)

                If Me.RResignation.Checked = True Then
                    Me.txtBenefits.Text = CDbl(Benfit * 0.33).ToString("N2")

                ElseIf Me.RTermination.Checked = True Then
                    Me.txtBenefits.Text = CDbl(Benfit * 0.66).ToString("N2")
                End If

            ElseIf Years >= 5 AndAlso Years < 10 Then
                Dim Benfit As Double = (2.5 * CDbl(Me.txtSalary.Text)) + ((Years - 5) * CDbl(Me.txtSalary.Text))
                If Me.RResignation.Checked = True Then
                    Me.txtBenefits.Text = Benfit * 0.33

                ElseIf Me.RTermination.Checked = True Then
                    Me.txtBenefits.Text = Benfit * 0.66
                End If

            ElseIf Years >= 10 Then
                Dim Benfit As Double = (2.5 * CDbl(Me.txtSalary.Text)) + ((Years - 5) * CDbl(Me.txtSalary.Text))
                Me.txtBenefits.Text = Benfit * 0.66
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ClearBenefits()
        Me.txtSalary.Clear()
        Me.txtPeriodHiredYears.Clear()
        Me.txtPeriodHiredMonths.Clear()
        Me.txtBenefits.Clear()
    End Sub

    Private Sub DTPEndOfService_ValueChanged(sender As System.Object, e As System.EventArgs) Handles DTPEndOfService.ValueChanged
        ClearBenefits()
    End Sub

    Private Sub RResignation_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RResignation.CheckedChanged
        ClearBenefits()
    End Sub

    Private Sub RTermination_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RTermination.CheckedChanged
        ClearBenefits()
    End Sub

    Private Sub ButClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        If Me.txtSalary.Text.Trim.Length = 0 Then
            MsgBox("Please calculate the benefits first")

        ElseIf MsgBox("Confirm end of service for: " & Me.txtNameEn.Text, MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim EndOfServiceType As String
                If Me.RResignation.Checked = True Then
                    EndOfServiceType = "Resignation"

                ElseIf Me.RTermination.Checked = True Then
                    EndOfServiceType = "Termination"
                End If

                Dim cmd As New SqlCommand("Update StaffProfiles " & _
                                          "Set EndOfServiceDate=@EndOfServiceDate,EndOfServiceType=@EndOfServiceType," & _
                                          "EndOfServiceSalary=@EndOfServiceSalary,HiredYears=@HiredYears,HiredMonths=@HiredMonths," & _
                                          "EndOfServiceBenefits=@EndOfServiceBenefits,EndOfServiceBenefitsWritten=@EndOfServiceBenefitsWritten," & _
                                          "Comments=@Comments,EndedBy=@EndedBy,EndedOn=GetDate() Where EmpNo=" & CInt(Me.txtEEmpNo.Text.Trim), cnn)

                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@EndOfServiceDate", Me.DTPEndOfService.Value)
                cmd.Parameters.AddWithValue("@EndOfServiceType", EndOfServiceType)
                cmd.Parameters.AddWithValue("@EndOfServiceSalary", CDbl(Me.txtSalary.Text))
                cmd.Parameters.AddWithValue("@HiredYears", CInt(Me.txtPeriodHiredYears.Text))
                cmd.Parameters.AddWithValue("@HiredMonths", CInt(Me.txtPeriodHiredMonths.Text))
                cmd.Parameters.AddWithValue("@EndOfServiceBenefits", CDbl(Me.txtBenefits.Text))
                cmd.Parameters.AddWithValue("@EndOfServiceBenefitsWritten", Me.txtWrittenValue.Text)
                cmd.Parameters.AddWithValue("@Comments", Me.txtComments.Text.Trim)
                cmd.Parameters.AddWithValue("@EndedBy", CurrentUser)

                cnn.Open()
                cmd.ExecuteNonQuery()
                cnn.Close()

                Me.Cursor = Cursors.Default

                MsgBox("Updated Successfully")

                Me.Close()
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub txtBenefits_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtBenefits.TextChanged
        Try
            If Me.txtBenefits.Text.Trim.Length = 0 Then
                Me.txtWrittenValue.Clear()
            Else
                Me.txtWrittenValue.Text = SpellNumber(CDbl(Me.txtBenefits.Text)).ToString()

                Me.txtWrittenValue.Text = Me.txtWrittenValue.Text.Replace("Dollar", "Riyal")
                Me.txtWrittenValue.Text = Me.txtWrittenValue.Text.Replace("and No Halala", "")
                Me.txtWrittenValue.Text = Me.txtWrittenValue.Text.Replace("Cent", "Halala")
                Me.txtWrittenValue.Text = Me.txtWrittenValue.Text & " Only"
            End If
        Catch ex As Exception
            Me.txtBenefits.Clear()
        End Try
    End Sub
End Class